package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import java.sql.ResultSet;

import bean.Student;
import ojdbc.ConnORCL;

public class SDao {
	public SDao() {};
	public Student GetStuSco(String stu_id)throws Exception
	{
		Student stu = new Student();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select  s.stu_name , c.c_name , ch.grade , ch.gpa , c.c_point , c.g_id , ch.gpa , c.c_point from student s join chooses ch on s.stu_id = ch.stu_id join course  c on ch.c_id = c.c_id and s.g_id = c.g_id where s.stu_id = ?";
		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu_id);
			rs = ps.executeQuery();
			while(rs.next())
			{
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getDouble(4));
				System.out.println(rs.getString(5));*/
				stu.setStu_name(rs.getString(1));
				stu.setCourses(rs.getString(2),rs.getDouble(3),rs.getDouble(4),rs.getDouble(5));
				stu.setG_id(rs.getString(6));
			}
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, null);
		}
		return stu;
	}
	
	public Student ChangeGrade(String stu_id,String g_id)throws Exception
	{
		Student stu = new Student();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select s.stu_name ,c.c_name , ch.grade , ch.gpa , c.c_point from student s join chooses ch on s.stu_id = ch.stu_id join course  c on ch.c_id = c.c_id where s.stu_id = ? and c.g_id = ?";
		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu_id);
			ps.setString(2, g_id);
			rs = ps.executeQuery();
			while(rs.next())
			{
/*				System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getDouble(4));
				System.out.println(rs.getString(5));*/
				stu.setStu_name(rs.getString(1));
				stu.setCourses(rs.getString(2),rs.getDouble(3),rs.getDouble(4),rs.getDouble(5));
			}
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, null);
		}
		return stu;
	}
	
	public Student StuClassTable(String stu_id)throws Exception
	{
		Student stu = new Student();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select c.c_name , c.c_time , c.c_point ,t.tea_name , s.g_id ,s.stu_name from classtable ct join student s on ct.cla_id = s.cla_id and ct.g_id = s.g_id join course c on c.c_id = ct.c_id  join teacher t on t.tea_id = ct.tea_id where s.stu_id = ?";
		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu_id);
			rs = ps.executeQuery();
			while(rs.next())
			{
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getDouble(4));
				System.out.println(rs.getString(5));*/
				stu.setClaTab(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getString(4));
				stu.setG_id(rs.getString(5));
				stu.setStu_name(rs.getString(6));
			}
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, null);
		}
		return stu;
	}
	
	public Student StuClaTab(String stu_id , String g_id)throws Exception
	{
		System.out.println(stu_id +"    "+ g_id);
		Student stu = new Student();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select c.c_name , c.c_time , c.c_point ,t.tea_name  from classtable ct join student s on ct.cla_id = s.cla_id join course c on c.c_id = ct.c_id  join teacher t on t.tea_id = ct.tea_id where s.stu_id = ? and ct.g_id = ?";
		try
		{
			conn = ConnORCL.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu_id);
			ps.setString(2, g_id);
			rs = ps.executeQuery();
			while(rs.next())
			{
				System.out.println(rs.getString(1));
				/*System.out.println(rs.getString(2));
				System.out.println(rs.getDouble(3));
				System.out.println(rs.getDouble(4));
				System.out.println(rs.getString(5));*/
				stu.setClaTab(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getString(4));
			}
		}
		finally
		{
			ConnORCL.ReleaseResource( conn, ps, null);
		}
		return stu;
	}
}
